package service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.DBConn;
import entities.CartEntity;
import entities.GoodsEntity;
import entities.UserEntity;

public class CartService {
	// 增加商品至购物车
	public static String addItem(String userName, String goodsId)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);
		GoodsEntity goods = GoodsService.getPointGoods(Integer
				.parseInt(goodsId));
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String nowTime = sdf.format(new Date());
		String sql = "insert into shop_orderitem  values( null ,'"
				+ goods.getSellerId() + "','" + goods.getId() + "','"
				+ user.getId() + "','1','0','" + nowTime
				+ "','wait_a_minute','0','0');";
		int result = stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		return result == 0 ? "failed" : "success";
	}

	// 删除某个商品
	public static String deleteItem(String userName, String goodsId)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);

		String sql = "delete from shop_orderitem where item_goodsId = "
				+ goodsId + " and item_userId = " + user.getId()
				+ " and item_state=0 and item_orderNo like 'wait_a_minute'";
		int rs = stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		return rs > 0 ? "success" : "failed";
	}

	// 清空购物车
	public static String deleteAll(String userName) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);

		String sql = "delete from shop_orderitem where item_userId = "
				+ user.getId()
				+ " and item_state=0 and item_orderNo like 'wait_a_minute'";
		int rs = stmt.executeUpdate(sql);
		stmt.close();
		conn.close();
		return rs > 0 ? "success" : "failed";
	}

	// 修改购物车商品的数量
	public static String updateItem(String userName, String goodsId,
			String update) throws SQLException {
		String result = "0";
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);
		String updateSql = "";
		if ("increase".equals(update)) {
			updateSql = " item_count+1 ";
		} else if ("decrease".equals(update)) {
			updateSql = " item_count-1 ";
		} else {
			updateSql = update;
		}

		String sql = "update shop_orderitem set item_count = " + updateSql
				+ " where item_goodsId =" + goodsId + " and item_userId = "
				+ user.getId()
				+ " and item_state=0 and item_orderNo like 'wait_a_minute'";
		int rs = stmt.executeUpdate(sql);
		if (rs > 0) {
			result = selectItem(userName, goodsId).get(0).getItemCount() + "";
		}
		stmt.close();
		conn.close();
		return result;
	}

	// 查询某一特定商品查询购物车是否有该商品
	public static List<CartEntity> selectItem(String userName, String goodsId)
			throws SQLException {
		List<CartEntity> cartList = new ArrayList<CartEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);
		String sql = "select * from  shop_orderitem where item_goodsId ="
				+ goodsId + " and item_userId = " + user.getId()
				+ " and item_state=0 and item_orderNo like 'wait_a_minute'";
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			CartEntity cart = new CartEntity();
			cart.setId(rs.getInt("item_id"));
			cart.setUserId(user.getId());
			cart.setGoodsId(rs.getInt("item_goodsId"));
			// cart.setGoodsName(rs.getString("goods_name"));
			// cart.setGoodsImg(rs.getString("goods_image"));
			// cart.setGoodsPrice(rs.getDouble("goods_price"));
			cart.setItemCount(rs.getInt("item_count"));
			cart.setItemState(rs.getInt("item_state"));
			cart.setOrderNo(rs.getString("item_orderNo"));
			cart.setSellerId(rs.getInt("item_sellerId"));
			// cart.setSellerName(rs.getString("seller_name"));
			cart.setItemTime(sdf.format(rs.getTimestamp("item_addtime")));

			cart.setItemIsComment(rs.getInt("item_isComment"));
			cart.setItemIsPaid(rs.getInt("item_isPaid"));

			cartList.add(cart);
		}
		rs.close();
		stmt.close();
		conn.close();
		return cartList;
	}

	// 查询购物车中所有商品
	public static List<CartEntity> selectAll(String userName)
			throws SQLException {
		List<CartEntity> cartList = new ArrayList<CartEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);
		// GoodsEntity goods =
		// GoodsService.getPointGoods(Integer.parseInt(goodsId));
		// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// String nowTime =sdf.format(new Date());

		String sql = " select  *  from shop_orderitem , shop_goods , shop_seller "
				+ " where item_goodsId = goods_id and goods_sellerId = seller_id "
				+ " and item_userId = "
				+ user.getId()
				+ " and item_state = 0 and item_orderNo like 'wait_a_minute'";

		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			CartEntity cart = new CartEntity();
			cart.setId(rs.getInt("item_id"));
			cart.setUserId(user.getId());
			cart.setGoodsId(rs.getInt("goods_id"));
			cart.setGoodsName(rs.getString("goods_name"));
			cart.setGoodsImg(rs.getString("goods_image"));
			cart.setGoodsPrice(rs.getDouble("goods_price"));
			cart.setItemCount(rs.getInt("item_count"));
			cart.setItemState(rs.getInt("item_state"));
			cart.setOrderNo(rs.getString("item_orderNo"));
			cart.setSellerId(rs.getInt("seller_id"));
			cart.setSellerName(rs.getString("seller_name"));
			cart.setItemTime(sdf.format(rs.getTimestamp("item_addtime")));

			cart.setItemIsComment(rs.getInt("item_isComment"));
			cart.setItemIsPaid(rs.getInt("item_isPaid"));

			cartList.add(cart);
		}
		rs.close();
		stmt.close();
		conn.close();
		return cartList;
	}

	// 查询某次次提交订单时购物车中的商品（针对订单提交成功后,以及订单查询时）
	public static List<CartEntity> selectRecent(String userName, String orderNo)
			throws SQLException {
		List<CartEntity> cartList = new ArrayList<CartEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);

		String sql = " select  *  from shop_orderitem , shop_goods , shop_seller "
				+ " where item_goodsId = goods_id and goods_sellerId = seller_id "
				+ " and item_userId = "
				+ user.getId()
				+ " and item_orderNo like '" + orderNo + "'";

		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			CartEntity cart = new CartEntity();
			cart.setId(rs.getInt("item_id"));
			cart.setUserId(user.getId());
			cart.setGoodsId(rs.getInt("goods_id"));
			cart.setGoodsName(rs.getString("goods_name"));
			cart.setGoodsImg(rs.getString("goods_image"));
			cart.setGoodsPrice(rs.getDouble("goods_price"));
			cart.setItemCount(rs.getInt("item_count"));
			cart.setItemState(rs.getInt("item_state"));
			cart.setOrderNo(rs.getString("item_orderNo"));
			cart.setSellerId(rs.getInt("seller_id"));
			cart.setSellerName(rs.getString("seller_name"));
			cart.setItemTime(sdf.format(rs.getTimestamp("item_addtime")));

			cart.setItemIsComment(rs.getInt("item_isComment"));
			cart.setItemIsPaid(rs.getInt("item_isPaid"));

			cartList.add(cart);
		}
		rs.close();
		stmt.close();
		conn.close();
		return cartList;
	}

	public static void main(String[] args) {
		try {
			System.out.println(selectItem("李文涛", "1"));
			System.out.println(selectAll("liwentao"));
			// System.out.println(deleteAll("liwentao"));
		} catch (SQLException e) {
			System.out.println("error");
			e.printStackTrace();
		}
	}
}
